package com.example.mylibrary.util;

import android.content.Context;
import android.util.Log;

import com.example.mylibrary.CallBack;
import com.example.mylibrary.bean.ItemGrops;
import com.example.mylibrary.bean.StuItem;
import com.example.mylibrary.bean.StuItem2;
import com.example.mylibrary.bean.StuItemGrop;
import com.example.mylibrary.data.DataDao;
import com.example.mylibrary.data.Item;
import com.example.mylibrary.data.ItemGrop;
import com.example.mylibrary.data.Schedule;
import com.example.mylibrary.data.Sort;
import com.example.mylibrary.data.StudentInfo;
import com.example.mylibrary.data.StudentItem;
import com.example.mylibrary.data.StudentItemGrop;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;


/**
 * Created by Administrator on 2017/12/27.
 */


public class ExlUtils {
	public static final int EXEL_READ_SUCCESS = 0X0;
	public static final int EXEL_READ_FAIL = 0X1;
	private static SimpleDateFormat fmt = new SimpleDateFormat(
			"yyyy-MM-dd HH:mm:ss");
	private static FormulaEvaluator evaluator;
	private ExlUtils.ExlListener listener;
	private static ExlUtils instance;

	public interface ExlListener {
		void onExlResponse(int responseCode);
	}

	public void setExlListener(ExlListener exlListener) {
		this.listener = exlListener;
	}

	private ExlUtils() {

	}

	public static ExlUtils getInstance() {
		if (instance == null) {
			instance = new ExlUtils();
		}
		return instance;
	}

	public void readExlData(final Context context, final String path) {

		new Thread() {
			@Override
			public void run() {
				super.run();
				try {
					List<List<String>> result = readXls(path);
					for (int i = 1; i < result.size(); i++) {
						Set set = new HashSet();

						StudentInfo studentInfo = new StudentInfo();
						Item item = new Item();
						Sort sort = new Sort();
						//学生基本信息表
						List<String> row = result.get(i);
						studentInfo.setStudenCode(row.get(0));
						studentInfo.setStudentName(row.get(1));
						if (row.get(2) != null) {
							studentInfo.setIdCard(row.get(2));
						} else {
							studentInfo.setIdCard("无身份证");
						}

						if (row.get(3) != null) {
							studentInfo.setIcCardNo(row.get(3));
						} else {
							studentInfo.setIcCardNo("无IC卡号");
						}
						studentInfo.setSex(row.get(4).equals("男") ? 0 : 1);
						if (row.get(5) != null) {
							studentInfo.setNation(row.get(5));
						} else {
							studentInfo.setNation("未知民族");
						}
						if (row.get(6) != null) {
							studentInfo.setBirthday(row.get(6));
						} else {
							studentInfo.setBirthday("未知生日");
						}
						if (row.get(7) != null) {
							studentInfo.setAddress(row.get(7));
						} else {
							studentInfo.setAddress("未知住址");
						}
						if (row.get(8) != null) {
							studentInfo.setSchool(row.get(8));
						} else {
							studentInfo.setSchool("未知学校");
						}

//                        if(row.get(9) != null){
//                            studentInfo.setGradeName(row.get(9));
//                        }else {
//                            studentInfo.setGradeName("未知年级");
//                        }
//
//                        if(row.get(10) != null){
//                            studentInfo.setClassName(row.get(10));
//                        }else {
//                            studentInfo.setGradeName("未知班级");
//                        }


//                        item.setItemCode(row.get(9));
						//                      item.setItemName(row.get(10));
						//                     sort.setSortName(row.get(11));

						//添加数据
						DataDao.insertInfo(studentInfo);


					}
					if (listener != null) {
						listener.onExlResponse(EXEL_READ_SUCCESS);
					}

				} catch (Exception e) {
					if (listener != null) {
						listener.onExlResponse(EXEL_READ_FAIL);
					}
					e.printStackTrace();
				}
			}

		}.start();
	}

	public void readExlData1(final Context context, final String path) {

		new Thread() {
			@Override
			public void run() {
				super.run();
				try {
					List<List<String>> result = readXls(path);

					for (int i = 1; i < result.size(); i++) {
						Set set = new HashSet();
						ItemGrop itemGrop = new ItemGrop();
						Item item = new Item();
						Sort sort = new Sort();
						//学生基本信息表
						List<String> row = result.get(i);

						itemGrop.setSex(Integer.valueOf(row.get(0)).intValue());
						itemGrop.setSortID(Long.valueOf(row.get(1)).longValue());
						itemGrop.setItemID(Integer.valueOf(row.get(2)).intValue());
						itemGrop.setGropNo(Integer.valueOf(row.get(3)).intValue());
						//添加数据 guangz
						DataDao.insertsort(sort);
						DataDao.insertitemGrop(itemGrop);

					}

					if (listener != null) {
						listener.onExlResponse(EXEL_READ_SUCCESS);
					}

				} catch (Exception e) {
					if (listener != null) {
						listener.onExlResponse(EXEL_READ_FAIL);
					}
					e.printStackTrace();
				}
			}

		}.start();
	}

	public void readExlData2(final Context context, final String path) {

		new Thread() {
			@Override
			public void run() {
				super.run();
				try {
					List<List<String>> result = readXls(path);

					for (int i = 1; i < result.size(); i++) {
						Set set = new HashSet();
						ItemGrop itemGrop = new ItemGrop();
						Item item = new Item();
						Sort sort = new Sort();
						//学生基本信息表
						List<String> row = result.get(i);
						item.setItemCode(row.get(0));
						item.setItemName(row.get(1));

						//添加数据
						DataDao.insertsort(sort);
						DataDao.insertitem(item);

						if (listener != null) {
							listener.onExlResponse(EXEL_READ_SUCCESS);
						}

					}

				} catch (Exception e) {
					if (listener != null) {
						listener.onExlResponse(EXEL_READ_FAIL);
					}
					e.printStackTrace();
				}
			}

		}.start();
	}

	public void readExlData3(final Context context, final String path, final CallBack callBack) {
		new Thread() {
			@Override
			public void run() {
				super.run();
				try {
					List<List<String>> result = readXls(path);


					//日期

					Set<Schedule> set = new HashSet();
					List<Schedule> schedules = new ArrayList<Schedule>();

					List<Item> items = new ArrayList<Item>();
					List<Sort> sorts = new ArrayList<Sort>();
					List<StudentInfo> studentInfos = new ArrayList<StudentInfo>();
					List<StuItemGrop> stuItemGrops = new ArrayList<StuItemGrop>();
					List<ItemGrops> itemGropses = new ArrayList<ItemGrops>();
					List<StuItem> stuItems = new ArrayList<StuItem>();
					for (int i = 1; i < result.size(); i++) {

						StudentInfo studentInfo = new StudentInfo();
						StuItem stuItem = new StuItem();
						Item item = new Item();
						Sort sort = new Sort();
						Schedule schedule = new Schedule();
						ItemGrops itemGrops1 = new ItemGrops();
						StuItemGrop stuItemGrop = new StuItemGrop();
						//学生基本信息表
						List<String> row = result.get(i);

						sort.setSortName(row.get(0));
						itemGrops1.setSortName(row.get(0));
						for (int j = 0; j < sorts.size(); j++) {
							if (sorts.get(j).getSortName() == sort.getSortName()) {
								sorts.remove(j);
							}
						}
						sorts.add(sort);
						//性别
						studentInfo.setSex(row.get(1).equals("男") ? 0 : 1);

						//上传状态默认0
						studentInfo.setUploadState(0);
						//检录状态默认0
						studentInfo.setRollCallState(0);

						//名字
						studentInfo.setStudentName(row.get(2));

						//地址
						if (row.get(3) != null) {
							studentInfo.setAddress(row.get(3));
						} else {
							studentInfo.setAddress("未知住址");
						}
						studentInfo.setSchool(row.get(3));
						//项目名称
						item.setItemName(row.get(4));
						stuItem.setItemName(row.get(4));
						itemGrops1.setItemName(row.get(4));
						for (int j = 0; j < items.size(); j++) {
							if (items.get(j).getItemName().equals(item.getItemName())) {
								items.remove(j);
							}
						}
						items.add(item);
						itemGrops1.setRace(Integer.valueOf(row.get(5)).intValue());

						schedule.setScheduleNo(Integer.valueOf(row.get(6)).intValue());
						itemGrops1.setScheduleNo(Integer.valueOf(row.get(6)).intValue());
						//  itemGrop.setGropNo(Integer.valueOf(row.get(9)).intValue());
						itemGrops1.setGropNo(Integer.valueOf(row.get(7)).intValue());

						stuItemGrop.setTrackNo(Integer.valueOf(row.get(8)).intValue());
						stuItemGrops.add(stuItemGrop);
						schedule.setScheduletime(row.get(9));
						itemGrops1.setScheduletime(row.get(9));
						for (int j = 0; j < schedules.size(); j++) {
							if (schedules.get(j).getScheduletime() == schedule.getScheduletime()) {
								schedules.remove(j);

							}
						}
						schedules.add(schedule);
						studentInfo.setStudenCode(row.get(10));
						stuItem.setStuCode(row.get(10));
						if (row.get(11) != null) {
							studentInfo.setIdCard(row.get(11));
						} else {
							studentInfo.setIdCard("无身份证");
						}


						studentInfos.add(studentInfo);
						itemGropses.add(itemGrops1);
						stuItems.add(stuItem);

					}

					DataDao.insertStus(studentInfos);
					DataDao.insertSchedules(schedules);
					DataDao.insertISorts(sorts);
					DataDao.insertItems(items);

					schedules = new ArrayList<Schedule>();
					sorts = new ArrayList<Sort>();
					items = new ArrayList<Item>();
					schedules = DataDao.querySchedulesAll();
					sorts = DataDao.querySortsAll();
					items = DataDao.queryItemAll();
					for (int z = 0; z < itemGropses.size(); z++) {
						ItemGrops igrop = itemGropses.get(z);
						for (Sort sor : sorts) {
							if ((sor.getSortName()).equals(igrop.getSortName())) {
								igrop.setSortID(sor.getSortID());
							}
						}
						for (Schedule sch : schedules) {
							if (sch.getScheduletime().equals(igrop.getScheduletime())) {
								igrop.setScheduleID(sch.getScheduleID());
							}
						}
						for (Item ite : items) {
							if (ite.getItemName().equals(igrop.getItemName())) {
								igrop.setItemID(ite.getItemID());
							}
						}
						itemGropses.set(z, igrop);
					}
					List<ItemGrop> itemGrops = new ArrayList<ItemGrop>();
					for (int k = 0; k < itemGropses.size(); k++) {
						ItemGrop itemGrop = new ItemGrop();
						itemGrop.setScheduleID(itemGropses.get(k).getScheduleID());
						itemGrop.setSex(itemGropses.get(k).getSex());
						itemGrop.setSortID(itemGropses.get(k).getSortID());
						itemGrop.setItemID(itemGropses.get(k).getItemID());
						itemGrop.setGropNo(itemGropses.get(k).getGropNo());
						itemGrop.setRace(itemGropses.get(k).getRace());
						DataDao.insertitemGrop(itemGrop);
					}
					List<StuItem2> stuItem2s = new ArrayList<StuItem2>();
					List<StudentInfo> stuInfo = new ArrayList<StudentInfo>();
					List<Item> items1 = new ArrayList<Item>();
					for (int g = 0; g < stuItems.size(); g++) {
						StudentItem stuItem2 = new StudentItem();
						Log.e("run: ", stuItems.get(g).getStuCode() + "");
						items1 = DataDao.queryItemId(stuItems.get(g).getItemName());
						for (Item it : items1) {
							stuItem2.setItemID(it.getItemID());
						}
						stuInfo = DataDao.queryStuId(stuItems.get(g).getStuCode());
						for (StudentInfo s : stuInfo) {
							stuItem2.setStudentID(s.getStudentID());
						}
						DataDao.insertStuItem(stuItem2);

					}

					List<ItemGrop> itemGroupAll = DataDao.itemGropAll();
					List<StudentItem> stuItemAll = DataDao.stuItemAll();
					List<StudentItemGrop> stuItemGrop = new ArrayList<StudentItemGrop>();

					for (int e = 0; e < itemGroupAll.size(); e++) {
						StudentItemGrop studentItemGrop = new StudentItemGrop();
						StuItemGrop stui = stuItemGrops.get(e);
						ItemGrop itemgropAll = itemGroupAll.get(e);
						StudentItem studentitem = stuItemAll.get(e);
						Log.e("itemgropAll: ", itemgropAll.getItemGropID() + "");
						Log.e("studentitem: ", studentitem.getStudentItemID() + "");
						studentItemGrop.setItemGroupID(itemgropAll.getItemGropID());
						studentItemGrop.setStudentItemID(studentitem.getStudentItemID());
						studentItemGrop.setTrackNo(stui.getTrackNo());
						DataDao.insertStuGrop(studentItemGrop);
					}

					if (listener != null) {
						listener.onExlResponse(EXEL_READ_SUCCESS);
					}
					callBack.onSucess("");

				} catch (Exception e) {
					if (listener != null) {
						listener.onExlResponse(EXEL_READ_FAIL);
					}
					e.printStackTrace();
					callBack.onFail(e.toString());
				}
			}

		}.start();
	}


	public List<List<String>> readXls(String path) throws Exception {

		InputStream is = new FileInputStream(path);
		// HSSFWorkbook 标识整个excel
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
		List<List<String>> result = new ArrayList<List<String>>();
		int size = hssfWorkbook.getNumberOfSheets();
		for (int numSheet = 0; numSheet < size; numSheet++) {
			// HSSFSheet 标识某一页
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			if (hssfSheet == null) {
				break;
			}
			for (int rowNum = 0; rowNum < hssfSheet.getPhysicalNumberOfRows(); rowNum++) {
				// HSSFRow表示行
				HSSFRow hssfRow = hssfSheet.getRow(rowNum);
				int minColIx = hssfRow.getFirstCellNum();
				int maxColIx = hssfRow.getLastCellNum();
				List<String> rowList = new ArrayList<String>();
				// 遍历该行，获取处理每个cell元素
				for (int colIx = minColIx; colIx < maxColIx; colIx++) {
					// HSSFCell 表示单元格
					HSSFCell cell = hssfRow.getCell(colIx);
					if (cell == null) {
						rowList.add("");

						continue;
					}
					String rowContent = getStringVal(cell);
					if (colIx == 5 || colIx == 6) {


					}
					rowList.add(rowContent);

				}
				result.add(rowList);
			}
		}
		hssfWorkbook.close();
		return result;
	}

	public static String getStringVal(HSSFCell cell) {
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
			case Cell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case Cell.CELL_TYPE_NUMERIC:
				cell.setCellType(Cell.CELL_TYPE_STRING);
				return cell.getStringCellValue();
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
			default:
				return "";
		}
	}
}
